﻿
CREATE PROCEDURE [dbo].[fares_GenereazaCodArticol]
	-- Add the parameters for the stored procedure here
	@radacina varchar(10),
	@codNou varchar(20) out
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	DECLARE @CodReturnat varchar(20)
	DECLARE @CodReturnatNumar varchar(20)
	DECLARE @CodReturnatNumarFilt varchar(20)
	DECLARE @valNumar decimal(18,0)
	DECLARE @i int
    -- Insert statements for procedure here
	SELECT @CodReturnat=Max(codarticol) from articole where codarticol like @radacina + '%'
	--PRINT('test:[' + @CodReturnat+ ']')
	if @CodReturnat is null
	begin 
		SET @CodReturnat = @radacina + '001'
	end
	else
	begin
		SET @CodReturnat = SUBSTRING(@CodReturnat,0,10)
		--PRINT('@CodReturnat:[' + @CodReturnat+ ']')
		SET @CodReturnatNumar = REPLACE(@CodReturnat,@radacina,'')
		--PRINT('@CodReturnatNumar:[' + @CodReturnatNumar+ ']')
		SET @valNumar = 1
		SET @i = 1
		SET @CodReturnatNumarFilt = ''
		--PRINT('Inainte de while')
		WHILE @i<= LEN(@CodReturnatNumar)
		BEGIN
			IF SUBSTRING(@CodReturnatNumar,@i,1) >='0' AND SUBSTRING(@CodReturnatNumar,@i,1)<='9'
				SET @CodReturnatNumarFilt = @CodReturnatNumarFilt + SUBSTRING(@CodReturnatNumar,@i,1)
			SET @i=@i+1
		END
		
		--PRINT('@CodReturnatNumarFilt:[' + @CodReturnatNumarFilt+ ']')
		
		IF LTRIM(RTRIM(@CodReturnatNumarFilt)) <> ''
		BEGIN	
			PRINT('@CodReturnatNumarFilt:[' + @CodReturnatNumarFilt+ ']')
			
			SET @valNumar = CAST(@CodReturnatNumarFilt AS decimal(20,0))
			SET @valNumar = @valNumar + 1
		END
			
		
		--PRINT('@valNumar:[' + @valNumar+ ']')
		SET @CodReturnat = CAST(@valNumar as varchar)
		--PRINT('@CodReturnat:[' + @CodReturnat+ ']')
		SET @CodReturnat = @radacina + REPLICATE('0',3-LEN(@CodReturnat)) + @CodReturnat
		--PRINT('@CodReturnat:[' + @CodReturnat+ ']')
		WHILE EXISTS(SELECT * from articole where codarticol = @CodReturnat)
		BEGIN
			SET @valNumar = @valNumar + 1
			SET @CodReturnat = CAST(@valNumar as varchar)
			SET @CodReturnat = @radacina + REPLICATE('0',3-LEN(@CodReturnat)) + @CodReturnat
		END

	end
	SET @codNou = @CodReturnat
END